package com.hzzh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.hzzh.util.ConnectionFactory;
import com.hzzh.vo.Meeting;

public class MeetingDAO {

	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;

	// 向表meeting中插入记录，其中status使用默认值0
	public int insert(Meeting meeting) {
		int meetingid = 0;
		conn = ConnectionFactory.getConnection();
		String sql = "insert into meeting"
				+ "(meetingname,roomid,reservationistid,numberofparticipants,starttime,endtime,reservationtime,canceledtime,description,status)"
				+ " values(?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, meeting.getMeetingname());
			ps.setInt(2, meeting.getRoomid());
			ps.setInt(3, meeting.getReservationistid());
			ps.setInt(4, meeting.getNumberofparticipants());
			ps.setTimestamp(5, meeting.getStarttime());
			ps.setTimestamp(6, meeting.getEndtime());
			ps.setTimestamp(7, meeting.getReservationtime());
			ps.setTimestamp(8, meeting.getCanceledtime());
			ps.setString(9, meeting.getDescription());
			ps.setString(10, "0");

			ps.executeUpdate();

			rs = ps.executeQuery("select max(meetingid) from meeting");
			if (rs.next()) {
				meetingid = rs.getInt(1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}
		return meetingid;
	}

	// 根据meeting，更新会议的状态，删除时间
	public void update(int meetingid, String status, Timestamp canceledtime) {
		conn = ConnectionFactory.getConnection();
		String sql = "update meeting set status=?,canceledtime=? where meetingid=?";

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, status);
			ps.setTimestamp(2, canceledtime);
			ps.setInt(3, meetingid);
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}

	}

	// 查询某员工预定的所有会议
	public List<Meeting> selectAllMeetingsByReserId(int reservationistid) {
		conn = ConnectionFactory.getConnection();
		List<Meeting> meetingslist = new ArrayList<Meeting>();
		Meeting mt = null;
		String sql = "select * from meeting where reservationistid=" + reservationistid;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				mt = new Meeting();
				mt.setMeetingid(rs.getInt("meetingid"));
				mt.setMeetingname(rs.getString("meetingname"));
				mt.setRoomid(rs.getInt("roomid"));
				mt.setReservationistid(rs.getInt("reservationistid"));
				mt.setNumberofparticipants(rs.getInt("numberofparticipants"));
				mt.setStarttime(rs.getTimestamp("starttime"));
				mt.setEndtime(rs.getTimestamp("endtime"));
				mt.setReservationtime(rs.getTimestamp("reservationtime"));
				mt.setCanceledtime(rs.getTimestamp("canceledtime"));
				mt.setDescription(rs.getString("description"));
				mt.setStatus(rs.getString("status"));
				meetingslist.add(mt);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}
		return meetingslist;
	}

	// 查询某id值对应的会议
	public Meeting selectById(int meetingid) {
		conn = ConnectionFactory.getConnection();

		Meeting meeting = null;
		try {
			ps = null;
			String sql = "select * from meeting where meetingid=" + meetingid;
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while (rs.next()) {
				meeting = new Meeting();
				meeting.setMeetingid(rs.getInt("meetingid"));
				meeting.setMeetingname(rs.getString("meetingname"));
				meeting.setRoomid(rs.getInt("roomid"));
				meeting.setReservationistid(rs.getInt("reservationistid"));
				meeting.setNumberofparticipants(rs.getInt("numberofparticipants"));
				meeting.setStarttime(rs.getTimestamp("starttime"));
				meeting.setEndtime(rs.getTimestamp("endtime"));
				meeting.setReservationtime(rs.getTimestamp("reservationtime"));
				meeting.setCanceledtime(rs.getTimestamp("canceledtime"));
				meeting.setDescription(rs.getString("description"));
				meeting.setStatus(rs.getString("status"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}
		return meeting;
	}

}
